﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace IJZ.SQLServer.StoredProcedureHelper
{
    /// <summary>
    /// StoredProcedureEngine class is main engine to generate stored procedures.
    /// </summary>
    public class StoredProcedureEngine
    {
        #region Private Properties
        /// <summary>
        /// Prefix holds the prefix of stored procedure.
        /// </summary>
        private string Prefix;

        /// <summary>
        /// AuthorName holds the prefix of stored procedure.
        /// </summary>
        private string AuthorName;
        #endregion

        #region Constructor
        /// <summary>
        /// Initializes a new instance of the class.
        /// </summary>
        public StoredProcedureEngine()
            : this(string.Empty, string.Empty)
        {

        }

        /// <summary>
        /// Initializes a new instance of the class.
        /// </summary>
        /// <param name="prefix"></param>
        /// <param name="authorName"></param>
        public StoredProcedureEngine(string prefix, string authorName)
        {
            this.Prefix = prefix;
            this.AuthorName = authorName;
        }
        #endregion

        #region Private Methods
        /// <summary>
        /// GetStoreProcedureName gets the name of stored procedure.
        /// </summary>
        /// <param name="prefix"></param>
        /// <param name="tableName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetStoreProcedureName(string prefix, string tableName, StoredProcedureType type)
        {
            if (string.IsNullOrEmpty(this.Prefix))
            {
                return string.Format("{0}_{1}", tableName, type.GetDescription());
            }
            else
            {
                return string.Format("{0}_{1}_{2}", prefix, tableName, type.GetDescription());
            }
        }

        /// <summary>
        /// GetStoredProcedureComments creates comments part for header of stored procedure.
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="tableName"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public string GetStoredProcedureComments(string spName, string tableName, StoredProcedureType type)
        {
            StringBuilder st = new StringBuilder();
            string typeDescription = string.Empty;

            switch (type)
            {
                case StoredProcedureType.Read:
                    typeDescription = "gets related record from";
                    break;
                case StoredProcedureType.ReadAll:
                    typeDescription = "gets all records from";
                    break;
                case StoredProcedureType.Delete:
                    typeDescription = "deletes record from";
                    break;
                case StoredProcedureType.Update:
                    typeDescription = "updates record in";
                    break;
                case StoredProcedureType.Create:
                    typeDescription = "adds new record in";
                    break;
            }

            st.Append("-- =============================================\n");
            st.Append(string.Format("-- Author:		{0}\n", this.AuthorName));
            st.Append(string.Format("-- Create date: {0}\n", DateTime.Now.ToString("MMMM, dd, yyyy")));
            st.Append(string.Format("-- Description:	{0} {1} {2}\n", spName, typeDescription, tableName));
            st.Append("-- =============================================\n");


            return st.ToString();
        }

        /// <summary>
        /// GetParameters process parameters for specified list of columns.
        /// </summary>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetParameters(DataRow[] Columns, StoredProcedureType type)
        {
            StringBuilder st = new StringBuilder();
            DataRow col; 
            if (Columns != null)
            {
                if (type == StoredProcedureType.Delete || type == StoredProcedureType.Read)
                {
                    col = this.GetIdentityKey(Columns);
                    if (col != null)
                    {
                        st.Append(this.GetParameter(col, false));
                    }
                }
                else if (type == StoredProcedureType.ReadAll)
                {
                    //do nothing
                }
                else //if (type == StoredProcedureType.Create || type == StoredProcedureType.Update)
                {
                    foreach (DataRow c in Columns)
                    {
                        if (type == StoredProcedureType.Create && Convert.ToBoolean(c["IsIdentityKey"]))
                        {
                            st.Append(this.GetParameter(c, true));
                        }
                        else
                        {
                            st.Append(this.GetParameter(c, false));
                        }
                    }
                }
            }

            return st.ToString().TrimEnd().TrimEnd('\n').TrimEnd(',');
        }

        /// <summary>
        /// GetParameter processes provided column.
        /// </summary>
        /// <param name="column"></param>
        /// <param name="isOut"></param>
        /// <returns></returns>
        private string GetParameter(DataRow column, bool isOut)
        {
            return this.GetParameter(column["COLUMN_NAME"].ToString(), column["DATA_TYPE"].ToString(), column["CHARACTER_MAXIMUM_LENGTH"], column["NUMERIC_PRECISION"], column["NUMERIC_SCALE"].ToString(), isOut);
        }

        /// <summary>
        /// GetParameter processes provided column.
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="dataType"></param>
        /// <param name="length"></param>
        /// <param name="precision"></param>
        /// <param name="scale"></param>
        /// <param name="isOut"></param>
        /// <returns></returns>
        private string GetParameter(string columnName, string dataType, object length, object precision, object scale, bool isOut)
        {
            switch (dataType.ToLower())
            {
                case "varchar":
                case "nvarchar":
                case "char":
                case "nchar":
                    if ((int)length==-1)
                    {
                        length = "MAX";
                    }
                    dataType = string.Format("{0}({1})", dataType, length);
                    break;
                case "decimal":
                case "numeric":
                    dataType = string.Format("{0}({1},{2})", dataType, precision, scale);
                    break;
            }

            if (isOut)
            {
                return string.Format("\t@{0}\t{1} OUTPUT,\n", columnName, dataType);
            }
            else
            {
                return string.Format("\t@{0}\t{1},\n", columnName, dataType);
            }
        }
        
        /// <summary>
        /// GetIdentityKey gets identity key column from provided list of columns.
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        private DataRow GetIdentityKey(DataRow[] Columns)
        {
            return Columns.FirstOrDefault(col => Convert.ToBoolean(col["IsIdentityKey"]));
        }

        /// <summary>
        /// GetPrimaryKey gets primary key column/s from provided list of columns.
        /// </summary>
        /// <param name="Columns"></param>
        /// <returns></returns>
        private DataRow[] GetPrimaryKey(DataRow[] Columns)
        {
            return Columns.Where(col => Convert.ToBoolean(col["IsPrimaryKey"])).ToArray();
        }


        /// <summary>
        /// GetColumn gets specified column from list of columns.
        /// </summary>
        /// <param name="Columns"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        private DataRow GetColumn(DataRow[] Columns, string columnName)
        {
            return Columns.FirstOrDefault(col => columnName.Equals(col["COLUMN_NAME"].ToString(), StringComparison.CurrentCultureIgnoreCase));
        }

        #region Query
        /// <summary>
        /// GetQuery process query part of stored procedure.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetQuery(string schema, string tableName, DataRow[] Columns, StoredProcedureType type)
        {
            StringBuilder st;
            DataRow identityColumn = this.GetIdentityKey(Columns);
            switch(type)
            {
                case StoredProcedureType.Read:
                case StoredProcedureType.ReadAll:
                    st = this.GetQueryRead(schema, tableName, Columns, type, identityColumn);
                    break;
                case StoredProcedureType.Delete:
                    st = this.GetQueryDelete(schema, tableName, Columns, type, identityColumn);                                       
                    break;
                case StoredProcedureType.Update:
                    st = this.GetQueryUpdate(schema, tableName, Columns, type, identityColumn);  
                    
                    break;

                case StoredProcedureType.Create:
                    st = this.GetQueryCreate(schema, tableName, Columns, type, identityColumn);                    
                    
                    break;
                default:
                    st = new StringBuilder();
                    break;
            }

            return st.ToString();
        }

        /// <summary>
        /// GetQueryRead process read and read all query part of stored procedure.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private StringBuilder GetQueryRead(string schema, string tableName, DataRow[] Columns, StoredProcedureType type, DataRow identityColumn)
        {
            StringBuilder st = new StringBuilder();

            st.Append("\tSelect \n\t\t");
            foreach (DataRow col in Columns)
            {
                st.Append(string.Format("{0}, ", col["COLUMN_NAME"]));
            }
            st.Remove(st.Length - 2, 2);
            st.Append(string.Format("\n\tFrom [{0}].[{1}]", schema, tableName));
            if (type == StoredProcedureType.Read && identityColumn != null)
            {
                st.Append(string.Format("\n\tWhere {0}=@{0}", identityColumn["COLUMN_NAME"]));
            }
            st.Append(";");

            return st;
        }
        
        /// <summary>
        /// GetQueryDelete process delete query part of stored procedure.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private StringBuilder GetQueryDelete(string schema, string tableName, DataRow[] Columns, StoredProcedureType type, DataRow identityColumn)
        {
            StringBuilder st = new StringBuilder();

            st.Append(string.Format("\tDelete From [{0}].[{1}]\n", schema, tableName));
            
            if (identityColumn != null)
            {
                st.Append(string.Format("\tWhere {0}=@{0};", identityColumn["COLUMN_NAME"]));
            }
            return st;
        }

        /// <summary>
        /// GetQueryUpdate process update query part of stored procedure.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private StringBuilder GetQueryUpdate(string schema, string tableName, DataRow[] Columns, StoredProcedureType type, DataRow identityColumn)
        {
            StringBuilder st = new StringBuilder();
            DataRow tempCol;

            st.Append(string.Format("\tUpdate [{0}].[{1}]\n\t\tSet \n", schema, tableName));

            foreach (DataRow colume in Columns)
            {
                if (!Convert.ToBoolean(colume["IsIdentityKey"]))
                {
                    st.Append(string.Format("\t\t\t{0}=@{0},\n", colume["COLUMN_NAME"]));
                }
            }

            st.Remove(st.Length - 2, 2);

            if (identityColumn != null)
            {
                st.Append(string.Format("\n\tWhere {0}=@{0};", identityColumn["COLUMN_NAME"]));
            }

            return st;
        }

        /// <summary>
        /// GetQueryCreate process create query part of stored procedure.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private StringBuilder GetQueryCreate(string schema, string tableName, DataRow[] Columns, StoredProcedureType type, DataRow identityColumn)
        {
            StringBuilder st = new StringBuilder();

            StringBuilder stValues = new StringBuilder();
            st.Append(string.Format("\tInsert Into [{0}].[{1}]\n\t\t\t\t(", schema, tableName));
            stValues.Append("\t\tValues\t(");
            foreach (DataRow colume in Columns)
            {
                if (!Convert.ToBoolean(colume["IsIdentityKey"]))
                {
                    st.Append(string.Format("{0},  ", colume["COLUMN_NAME"]));
                    stValues.Append(string.Format("@{0}, ", colume["COLUMN_NAME"]));
                }
            }

            st.Remove(st.Length - 3, 3);
            stValues.Remove(stValues.Length - 2, 2);

            st.Append(")\n");
            stValues.Append(");\n");

            st.Append(stValues);
            if (identityColumn != null)
            {
                st.Append(string.Format("\n\tSelect @{0}=@@Identity;", identityColumn["COLUMN_NAME"]));
            }
            return st;
        }
        #endregion

        /// <summary>
        /// GetStoredProcedure process and create stored procedure of specified type for given columns.
        /// </summary>
        /// <param name="schema"></param>
        /// <param name="tableName"></param>
        /// <param name="Columns"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        private string GetStoredProcedure(string schema, string tableName, DataRow[] Columns, StoredProcedureType type)
        {
            StringBuilder st = new StringBuilder();
            string spName = this.GetStoreProcedureName(this.Prefix, tableName, type);

            st.Append(this.GetStoredProcedureComments(schema, tableName, type));

            st.Append(string.Format("Create Procedure [{0}].[{1}]\n", schema, spName));

            st.Append(this.GetParameters(Columns, type) + "\nAs\n");

            st.Append(this.GetQuery(schema, tableName, Columns, type));

            st.Append("\nGo\n\n");

            return st.ToString();
        }
        #endregion

        #region Public Methods
        /// <summary>
        /// GetStoredProcedures process and create store procedures for all tables.
        /// </summary>
        /// <param name="tableData">DataTable containing list of tables from INFORMATION_SCHEMA.TABLES</param>
        /// <param name="columnData">DataTable containing list of all related columns from INFORMATION_SCHEMA.COLUMNS</param>
        /// <returns></returns>
        public string GetStoredProcedures(DataTable tableData, DataTable columnData)
        {
            StringBuilder st = new StringBuilder();
            
            foreach (DataRow tbl in tableData.Rows)
            {
                string tableName = tbl["TABLE_NAME"].ToString();
                string schema = tbl["TABLE_SCHEMA"].ToString();
                
                DataRow[]columns = columnData.Select(string.Format("TABLE_NAME='{0}'", tableName));

                st.Append(this.GetStoredProcedure(schema, tableName, columns, StoredProcedureType.Create));
                st.Append(this.GetStoredProcedure(schema, tableName, columns, StoredProcedureType.Read));
                st.Append(this.GetStoredProcedure(schema, tableName, columns, StoredProcedureType.ReadAll));
                st.Append(this.GetStoredProcedure(schema, tableName, columns, StoredProcedureType.Update));
                st.Append(this.GetStoredProcedure(schema, tableName, columns, StoredProcedureType.Delete));
            }

            return st.ToString();
        }


        #endregion
    }
}
